﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Classes;

namespace FlightSystemDBLayer
{
    public class DBFlight : IFlight
    {
        IList<City> realCities;
        SQLAccess co;
        public DBFlight(IList<City> realCities)
        {
             this.realCities = realCities;
            co = new SQLAccess();
        }

        public IList<Flight> GetFlights()
        {
            DataSet ds = new DataSet();
            IList<Flight> list = new List<Flight>();
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = new SqlCommand(@"select * FROM Flight", co.dbconn);
            da.Fill(ds);

            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                list.Add(CreateFlight(Convert.ToInt32(dr["IDDepartureCity"]), Convert.ToInt32(dr["IDArrivalCity"]), dr["Company"].ToString(), Convert.ToInt32(dr["NumberOfSeats"]), Convert.ToInt32(dr["IDFlight"]), Convert.ToDateTime(dr["DepartureTime"]), Convert.ToDateTime(dr["ArrivalTime"]), Convert.ToInt32(dr["PricePerSeat"])));
            }
            return list;

        }



        public IList<Flight> GetSpecifiedFlights(int noOfSeats, DateTime departureTime, DateTime departureEndTime, City destination)
        {
            DataSet ds = new DataSet();
            IList<Flight> list = new List<Flight>();
            DataSet ds2 = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter();


            SqlDataAdapter da2 = new SqlDataAdapter();

            da2.SelectCommand = new SqlCommand(@"select * FROM Flight WHERE IDArrivalCity = '" + destination.Id +"'", co.dbconn);
            da2.Fill(ds2);
            DateTime biggestValue = DateTime.MinValue;
            foreach (DataRow dr in ds2.Tables[0].Rows)
            {

                if (Convert.ToDateTime(dr["DepartureTime"]) > biggestValue)
                {
                    biggestValue = Convert.ToDateTime(dr["DepartureTime"]);
                
                }

            }

            da.SelectCommand = new SqlCommand(@"select * FROM Flight WHERE NumberOfSeats > " + noOfSeats + "AND DepartureTime < " + "'" + biggestValue + "' AND DepartureTime >" + "'" + departureTime + "' AND DepartureTime <" + "'" + departureEndTime + "'", co.dbconn);
            da.Fill(ds);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
               // list.Add(CreateFlight(Convert.ToInt32(dr["IDDepartureCity"]), Convert.ToInt32(dr["IDArrivalCity"]), dr["Company"].ToString(), Convert.ToInt32(dr["NumberOfSeats"]), Convert.ToInt32(dr["IDFlight"])));
               
                    list.Add(CreateFlight(Convert.ToInt32(dr["IDDepartureCity"]), Convert.ToInt32(dr["IDArrivalCity"]), dr["Company"].ToString(), Convert.ToInt32(dr["NumberOfSeats"]), Convert.ToInt32(dr["IDFlight"]), Convert.ToDateTime(dr["DepartureTime"]), Convert.ToDateTime(dr["ArrivalTime"]), Convert.ToInt32(dr["PricePerSeat"])));
                
                
               
            }
            return list;

        }
        //CreateFlight(int cityFromID, int cityToID, string company, int noOfSeats, int id, DateTime departure, DateTime arrival, int price)

        public Flight CreateFlight(int cityFromID, int cityToID, string company, int noOfSeats, int id, DateTime departure, DateTime arrival, int price)/*int cityFromID, int cityToID, string company, int noOfSeats, int id*/
        {
            
            SqlDataAdapter da = new SqlDataAdapter();
            DataSet ds1 = new DataSet();
            DataSet ds2 = new DataSet();
            City from = null;
            City to = null;


            da.SelectCommand = new SqlCommand(@"select * FROM City WHERE IDCity = " + cityFromID + "", co.dbconn);
            da.Fill(ds1);
            da.SelectCommand = new SqlCommand(@"select * FROM City WHERE IDCity = " + cityToID + "", co.dbconn);
            da.Fill(ds2);

            
            foreach (City c in realCities)
            {
                if (c.Name.Equals(ds1.Tables[0].Rows[0][1].ToString())) // Tables[number of table].rows[number of row][number of column] we use 1 because name is 
                {                                                  //located in 2nd column, we can change it to string e.a "Name" to get the same result
                    from = c;
                }

            }

            
            foreach (City f in realCities)
            {
                if (f.Name.Equals( ds2.Tables[0].Rows[0][1].ToString() ))
                {
                    to = f;
                }
            }
            Flight flightObject = new Flight(from, to);
            flightObject.Id = id;
            flightObject.NoOfSeats = noOfSeats;
            flightObject.DepartureTime = departure;
            flightObject.ArrivalTime = arrival;
            flightObject.PriceOfTheOneSeat = price;

            return flightObject;
         }


        //public Flight FindFlightByID(int idFlight)
        //{
        //    DataSet ds = new DataSet();
        //    SqlDataAdapter da = new SqlDataAdapter();
        //    da.SelectCommand = new SqlCommand(@"select * FROM Flight WHERE IDFlight = " + idFlight + "", SQLAccess.dbconn);
        //    da.Fill(ds);
        //    DataRow dr = ds.Tables[0].Rows[0];
        //    Flight flight = CreateFlight(Convert.ToInt32(dr["IDDepartureCity"]), Convert.ToInt32(dr["IDArrivalCity"]), dr["Company"].ToString(), Convert.ToInt32(dr["NumberOfSeats"]));
        //    return flight;

        //}

    }
}